package utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Objects;
import java.util.Properties;

public final class DBHelper {

	/**表示连接哪个数据库 */
	private static String database ; 
	/** 驱动 */
	private static String driverClassName ; 
	/**用户名 */
	private static String username ; 
	/**密码*/
	private static String password ; 
	/**协议 */
	private static String url ; 
	/** 是否是自动提交事务 */
	private static boolean autocommit ; 
	/** 设置事务的隔离级别 */
	private static int isolation ; 
	/**连接*/
	private static Connection conn ; 
	/**
	 * 期望不能被实例化
	 */
	private DBHelper() {}
	/**
	 * 使用静态代码块来加载配置文件中的信息
	 */
	static { config() ; }
	/**
	 * 读取配置文件 
	 */
	private static void config() {
		Properties p = new Properties() ; 
		InputStream ins = DBHelper.class.getResourceAsStream("/db.properties") ;
		try {
			p.load( ins );
			database = p.getProperty("connect") ; 
			driverClassName = p.getProperty(database + ".driver") ;
			username = p.getProperty(database + ".username") ;
			password = p.getProperty(database + ".password") ;
			url = p.getProperty(database + ".url") ;
			String autoCommit = p.getProperty("autocommit") ;
			autocommit = Boolean.valueOf(autoCommit) ; 
			String tx_isolation = p.getProperty("transactionIsolation") ;
			isolation = Integer.valueOf(tx_isolation) ; 
			if( isolation  <= 0 || (isolation != 1 && isolation != 2 && isolation != 4 && isolation != 8 )) {
				isolation = 2 ; 
			}
		} catch (IOException e) {
			System.out.println("读取配置文件失败" + e.getMessage() );
		}
	}

	/**
	 * 加载驱动
	 */
	private static void load() {
		try {
			Class.forName(driverClassName) ;
		} catch (ClassNotFoundException e) {
			System.out.println("加载驱动失败 ：" + e.getMessage() );
		} 
	}

	/**
	 * 验证Connection是否是有效的，且没有关闭 
	 * @return true 表示连接有效
	 * 				 false 表示连接无效
	 */
	private static boolean isValidate() {
		if( Objects.nonNull(conn) ) {
			// 判断没有关闭，是有效的
			try {
				/**
				 * isClosed 如果被关闭了返回 true
				 * isValid  如果是没有关闭的且  有效的，返回 true 
				 */
				return conn.isValid(3) ;
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}
		return false;
	}
	
	/**
	 * 建立连接
	 */
	public static Connection connect(){
		load() ; 
		try {
			if( !isValidate() ) { // 判断conn如果是空，或是无效的，则创建Connection对象
				conn = DriverManager.getConnection(url , username , password ) ; 
			}
		} catch (SQLException e) {
			System.out.println("创建连接失败 ：" + e.getMessage() );
		}
		return conn ; 
	}
	
	/**
	 * 设置事务是否自动提交、隔离级别
	 */
	public static void setTransaction() {
		if( !isValidate() ) {
			conn = connect() ; 
		}
			try {
				conn.setAutoCommit(autocommit);
			} catch (SQLException e) {
				System.out.println("设置是否自动提交事务失败 " + e.getMessage() );
			}
			try {
				conn.setTransactionIsolation(isolation);
			} catch (SQLException e) {
				System.out.println("设置隔离级别失败 ：" + e.getMessage() );
			}
	}
	/**
	 * 创建 Statement 对象 
	 * @return Statement 对象 
	 */
	public static Statement state() {
		Statement st = null ; 
		conn = connect() ; // 内部就已经做了有效无效的验证
		setTransaction() ;
		try {
			st = conn.createStatement() ;
		} catch (SQLException e) {
			System.out.println("创建Statement对象失败 ： " + e.getMessage() );
		} 
		return st ; 
	}

	/**
	 * 创建 PreparedStatement 对象 
	 * @param SQL 表示要预编译的SQL语句
	 * @param autoGeneratedKeys 是否要获取自动生成的键
	 * 			true表示 获取 ，false表示不获取
	 * @return PreparedStatement 对象 
	 */
	public static PreparedStatement prestate( String SQL ,  boolean autoGeneratedKeys ) {
		PreparedStatement ps = null ; 
		conn = connect() ;
		setTransaction() ;
		try {
			if( autoGeneratedKeys ) {
					ps = conn.prepareStatement(SQL , Statement.RETURN_GENERATED_KEYS ) ;
			}else {
					ps = conn.prepareStatement(SQL) ;
			}
		} catch (SQLException e) {
			System.out.println("创建PreparedStatement失败 : " + e.getMessage());
		} 
		return ps ; 
	}

	/**
	 * 执行DDL、DML
	 */
	public static boolean execute(String SQL , Object...params ) {
		boolean result  = false ; 
		if( Objects.isNull( SQL ) || SQL.trim().isEmpty() || SQL.trim().toLowerCase().startsWith("select")) {
			throw new RuntimeException("你传入的是空串或者是查询语句") ; 
		} 
		SQL = SQL.trim() ;
		SQL = SQL.toLowerCase() ; 
		String prefix = SQL.substring(0, SQL.indexOf(" " )) ;
		String opreation = "" ; 
		switch (prefix) {
		case "insert":	 opreation="插入数据" ; 	break;
		case "update":	 opreation="更新数据" ;  	break;
		case "delete":	  opreation="删除数据" ; 	break;
		case "truncate":	 opreation="截断表" ;  	break;
		case "create":	 opreation="创建表" ;  	break;
		case "drop":	 opreation="删除表" ; 	break;
		case "alter":	  opreation="修改表" ; 	break;
		case "rename":	  opreation="重命名表" ; 	break;
		}
		Connection c = null ; 
		if( params.length > 0 ) {
			PreparedStatement ps = prestate(SQL, false) ; 
			try {
				c = ps.getConnection() ;
			} catch (SQLException e1) {
				e1.printStackTrace();
			} 
			try {
				for (int i = 0; i < params.length; i++) {
					ps.setObject( i + 1, params[i]);
				}
				ps.executeUpdate() ; 
				/**是否要自动提交 */
				commit(c) ; 
				result = true ; 
			} catch (SQLException e) {
				System.out.println(opreation +"操作失败" + e.getMessage());
				/**应该考虑回滚 */
				rollback(c);
			}
		}else {
			Statement st = state() ; 
			try {
				c = st.getConnection() ;
			} catch (SQLException e1) {
				e1.printStackTrace();
			} 
			try {
				st.executeUpdate(SQL) ;
				/***考虑事务的提交*/
				commit(c);
				result = true ; 
			} catch (SQLException e) {
				System.out.println(opreation +"操作失败" + e.getMessage());
				/**应该考虑回滚*/
				rollback( c ) ; 
			} 
		}
		return result;
	}

	/**
	 * 处理事务 
	 */
	private static void commit(Connection c ) {
		if( Objects.nonNull(c) && !autocommit ) {
			try {
				c.commit();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}
	}
	private static void rollback(Connection c ) {
		if( Objects.nonNull(c) && !autocommit ) {
			try {
				c.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}
	}

	public static ResultSet query( String SQL , Object...params ) {
		ResultSet rs = null ; 
		if( Objects.isNull( SQL ) || SQL.trim().isEmpty() || !SQL.trim().toLowerCase().startsWith("select")) {
			throw new RuntimeException("你传入的是空串或者不是查询语句") ; 
		} 
		if( params.length > 0) {
			PreparedStatement  ps = prestate(SQL, false) ;
			try {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i+1, params[i]); 
				}
				rs = ps.executeQuery() ;
			} catch (SQLException e) {
				System.out.println("查询失败" + e.getMessage() );
			} 
		}else {
			Statement st = state() ;
			try {
				rs = st.executeQuery(SQL) ;
			} catch (SQLException e) {
				System.out.println("查询失败" + e.getMessage() );
			} 
		}
		return rs ; 
	}
	/**
	 * 处理结果集
	 */
	public static void meta( ResultSet rs ) {
		if( Objects.nonNull(rs) ) {
			ResultSetMetaData rsmd = null ; 
			try {
				rsmd = rs.getMetaData() ;
				int count = rsmd.getColumnCount() ;
				while( rs.next() ) {
					for (int i = 0; i < count; i++) {
						System.out.print(rs.getObject(i+1));
					}
					System.out.println();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
		}
	}
	/**
	 * 关闭资源
	 */
	public static void release (Object o ) {
		if(Objects.nonNull(o)) {
			if( o instanceof Connection ) {
				Connection c = (Connection)o;
				try {
					c.close();
				} catch (SQLException e) {
					System.out.println("关闭连接失败" + e.getMessage() );
				}
			}
			if( o instanceof ResultSet ) {
				ResultSet c = (ResultSet)o;
				try {
					c.close();
				} catch (SQLException e) {
					System.out.println("关闭结果集失败" + e.getMessage() );
				}
			}
			if( o instanceof Statement ) {
				Statement c = (Statement)o;
				try {
					c.close();
				} catch (SQLException e) {
					System.out.println("关闭Statement失败" + e.getMessage() );
				}
			}
		}
	}
	
}
